package com.topisv.tms.service.stock.instorageNew;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.topisv.tms.repository.stock.instorage.InstorageCountTaskDao;

@Service
@Transactional(readOnly = true)
public class InstorageScanService {
	@Autowired
	  private JdbcTemplate jdbcTemplate;
	/**
	 * 扫描界面的显示
	 * @param billId
	 * @return
	 */
	public List<Map<String,Object>> getInstorageScanDetail(Long billId){
	   String sql="select a.sku_code as sku,a.batch_code as batchCode, a.product_date as productDate, a.expire_date as expireDate,a.sku_name as skuName,a.goods_num as goodsNum,coalesce(b.count_num,0) as countNum,coalesce(b.up_num,0) as upNum,a.error_flag as errorFlag from "+
			   "(select d.*,e.sku_name from (select c.*,a.bill_code,a.customer_code from instorage_bill a,instorage_bill_pre b,instorage_bill_item_pre c where a.about_code=b.bill_code and b.Id=c.bill_pre_id and a.id="+billId+") d,edi_goods_sku e where  d.customer_code=e.customer_code and d.sku_code=e.sku_id ) a "+ 
			   "LEFT JOIN "+
			   "(select bill_code,sku,batch_code,product_date,expire_date,sum(coalesce(count_num,0)) as count_num,sum(coalesce(up_num,0)) as up_num from tt_up_task where status <>'3' group by bill_code,sku,batch_code,product_date,expire_date )b "+
			   "on a.bill_code=b.bill_code and a.sku_code=b.sku and IFNULL(a.batch_code,'') = IFNULL(b.batch_code,'') and IFNULL(a.product_date,'') = IFNULL(b.product_date,'') "
			   + "and IFNULL(a.expire_date,'') = IFNULL(b.expire_date,'') order by a.id";
	   return jdbcTemplate.queryForList(sql);
	}
	/**
	 * 检查LPN是否可用
	 * @param LPN
	 * @return
	 */
	public List<Map<String,Object>> checkLPN(String LPN,String warehouseCode){
		   String sql="select id  from lpn where warehouse_code='"+warehouseCode+"' and lpn_no='"+LPN+"' and status=0 and storage_type=0 ";
		   return jdbcTemplate.queryForList(sql);
	}
	
	public List<Map<String,Object>> checkUpLPN(String LPN,String warehouseCode){
		   String sql="select id  from lpn where FIND_IN_SET(warehouse_code,'"+warehouseCode+"') and lpn_no='"+LPN+"'";
		   return jdbcTemplate.queryForList(sql);
	}
	
	public int updateLPNStatus(String id,String status,String billCode){
		   String sql="update lpn set status='"+status+"', order_no='"+billCode+"' where id='"+id+"'  ";
		   return jdbcTemplate.update(sql);
	}
	/**
	 * 
	 */
	public int updateInstorageBillPreStatus(String billCode){
		String sql="update instorage_bill_pre set order_status='2' where bill_code='"+billCode+"'";
		 return jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据入库单号、SKU统计收货明细表中的收货数量
	 * @param billCode
	 * @param sku
	 * @return
	 */
	public List<Map<String,Object>> getReceiptDetailByBillCodeAndSKU(String billCode,String sku){
		String sql="select bill_code as billCode,sku as sku,sum(coalesce(count_num,0)) as countNum from tt_up_task where bill_code='"+billCode+"' and sku='"+sku+"' and status <>'3'  group by bill_code,sku ";
		return jdbcTemplate.queryForList(sql);
	}
	
	/**
     * 根据入库单号、SKU、批次号、生产日期、过期日期统计收货明细表中的收货数量
     * @param billCode
     * @param sku
     * @return
     */
	public List<Map<String,Object>> getReceiptDetailByBillCodeAndSKUNew(String billCode,String sku,String batchCode, String productDate, String expireDate){
        String sql = "select bill_code as billCode, sku as sku, batch_code as batchCode, product_date as productDate, expire_date as expireDate," +
            " sum(coalesce(count_num,0)) as countNum from tt_up_task where bill_code = '" + billCode + "' and sku = '" + sku + "' and batch_code = '" + 
            batchCode + "' and product_date = '" + productDate + "' and expire_date = '" + expireDate +"' and status <> '3' group by bill_code, sku, batch_code," +
            "product_date, expire_date";
        return jdbcTemplate.queryForList(sql);
    }
	/**
	 * 根据入库单和sku预约入库明细表
	 * @param billCode
	 * @param sku
	 * @return
	 */
	public List<Map<String,Object>> getBillItemPreByBillCodeAndSKU(String billCode,String sku){
		String sql="select a.bill_code,c.sku_code,sum(c.goods_num) as goods_num  from instorage_bill a,instorage_bill_pre b,instorage_bill_item_pre c "
				+ " where a.about_code=b.bill_code and b.Id=c.bill_pre_id and newstatus not in ('7','8') and a.bill_code='"+billCode+"'"
						+ " and c.sku_code='"+sku+"' group by bill_code,sku_code ";
		return jdbcTemplate.queryForList(sql);
	}
	
	/**
     * 根据入库单和sku、批次号、生产日期、过期日期预约入库明细表
     * @param billCode
     * @param sku
     * @return
     */
    public List<Map<String,Object>> getBillItemPreByBillCodeAndSKUNew(String billCode,String sku, String batchCode, String productDate, String expireDate){
        String sql="select a.bill_code,"
            + "c.sku_code,"
            + "c.batch_code,"
            + "c.product_date,"
            + "c.expire_date,"
            + "sum(c.goods_num) as goods_num  "
            + "from instorage_bill a,"
            + "instorage_bill_pre b,"
            + "instorage_bill_item_pre c "
            + " where a.about_code=b.bill_code "
            + "and b.Id=c.bill_pre_id "
            + "and newstatus not in ('7','8') "
            + "and a.bill_code='"+billCode+"'"
            + " and c.sku_code='"+sku+"' "
            + " and (c.batch_code is null or c.batch_code = '"+batchCode 
            + "') and (c.product_date is null or c.product_date = '"+ productDate 
            + "') and (c.expire_date is null or c.expire_date = '"+ expireDate 
            + "') group by bill_code,sku_code,batch_code,product_date,expire_date";
        return jdbcTemplate.queryForList(sql);
    }
	
	/**
	 * 根据入库单统计所有的预约入库明细记录条数
	 * @param billCode
	 * @return
	 */
	public List<Map<String,Object>> getAllBillItemPre(String billCode){
		String sql="select c.bill_pre_id,"
		    + "c.sku_code,"
		    + "c.batch_code,"
		    + "c.product_date,"
		    + "c.expire_date"
		    + " from instorage_bill a,"
		    + "instorage_bill_pre b,"
		    + "instorage_bill_item_pre c "
			+ "where a.about_code=b.bill_code "
			+ "and b.Id=c.bill_pre_id "
			+ "and a.newstatus not in ('7','8') "
			+ "and a.bill_code='"+billCode
			+"' group by c.bill_pre_id, c.sku_code, c.batch_code, c.product_date, c.expire_date";
		return jdbcTemplate.queryForList(sql);
	}
	/**
	 *判断是部分收货还是完全收货
	 */
	public List<Map<String,Object>> loadAllReceiptDetailSKUNo(Long billId){
		String sql="select *  from "+
				"(select c.*,a.bill_code from instorage_bill a,instorage_bill_pre b,instorage_bill_item_pre c where a.about_code=b.bill_code and b.Id=c.bill_pre_id and a.newstatus not in ('7','8') and a.id="+billId+") a ,"+
				"(select bill_code,sku,sum(coalesce(count_num,0)) as count_num,sum(coalesce(up_num,0)) as up_num from tt_up_task where status <>'3' group by bill_code,sku )b "+
				"where a.bill_code=b.bill_code and a.sku_code=b.sku and a.goods_num=b.count_num ";
		return jdbcTemplate.queryForList(sql);
	}
	
	/**
     *判断是部分收货还是完全收货
     */
    public List<Map<String,Object>> loadAllReceiptDetailSKUNoNew(Long billId){
        //update LiBin 20161027 bug590 修改SQL IFNULL(***,'') ****
        String sql="select * from "
                + "(select IFNULL(c.batch_code,'') batch_code,a.bill_code,"
                + " IFNULL(c.product_date,'') product_date,"
                + " IFNULL(c.expire_date,'') expire_date,"
                + " c.sku_code,c.goods_num "
                + "from instorage_bill a,"
                + "instorage_bill_pre b,"
                + "instorage_bill_item_pre c "
                + "where a.about_code=b.bill_code "
                + "and b.Id=c.bill_pre_id "
                + "and a.newstatus not in ('7','8') "
                + "and a.id="+billId+") a ,"+
                "(select bill_code,"
                + "sku,"
                + "batch_code,"
                + "product_date,"
                + "expire_date,"
                + "sum(coalesce(count_num,0)) as count_num,"
                + "sum(coalesce(up_num,0)) as up_num "
                + "from tt_up_task where status <>'3' "
                + "group by bill_code,sku,batch_code,product_date,expire_date) b "+
                "where a.bill_code=b.bill_code "
                + "and a.sku_code=b.sku "
                + "and a.batch_code = b.batch_code "
                + "and a.product_date = b.product_date "
                + "and a.expire_date = b.expire_date "
                + "and a.goods_num=b.count_num ";
        return jdbcTemplate.queryForList(sql);
    }
	/**
	 * 根据入库单统计所有sku的收获明细
	 * @param billCode
	 * @param sku
	 * @return
	 */
	public List<Map<String,Object>> getReceiptDetailByBillCode(String billCode){
		String sql="select id,bill_code as billCode,sku ,sku_name as skuName,lpn_no as lpnNo,coalesce(count_num,0) as countNum,coalesce(up_num,0) as upNum,error_flag as errorFlag,stock_code as stockCode,print_flag as printFlag "
				+ " from tt_up_task where bill_code='"+billCode+"' and status <>'3'  order by  lpn_update_time ";
		return jdbcTemplate.queryForList(sql);
	}
	
	
	/**
	 * 统计该入库单中是否还有其他LPN号没有完全上架
	 */
	public List<Map<String,Object>> getReceiptDetailUnUpCountByBillCode(String billCode){
		String sql="select bill_code as billCode,lpn_no as lpnNo,count(*) as unUpCount "
				+ " from tt_up_task where bill_code='"+billCode+"' and status in ('0','1')  group by bill_code,lpn_no ";
		return jdbcTemplate.queryForList(sql);
	}
	/**
	 * 根据入库单号统计收货明细表中所有SKU的收货数量
	 * @param billCode
	 * @param sku
	 * @return
	 */
	public List<Map<String,Object>> getReceiptDetailSKUByBillCode(String billCode){
		String sql="select sku as sku,error_flag as errorFlag,sum(coalesce(count_num,0)) as countNum,sum(coalesce(up_num,0)) as upNum from tt_up_task where bill_code='"+billCode+"'  and status <>'3'  group by sku,error_flag ";
		return jdbcTemplate.queryForList(sql);
	}
	
	/**
     * 根据入库单号统计收货明细表中所有SKU的收货数量
     * @param billCode
     * @param sku
     * @return
     */
    public List<Map<String,Object>> getReceiptDetailSKUByBillCodeNew(String billCode){
        String sql="select sku as sku,batch_code as batchCode, product_date as productDate, expire_date as expireDate, error_flag as errorFlag,"
            + "sum(coalesce(count_num,0)) as countNum,sum(coalesce(up_num,0)) as upNum from tt_up_task where bill_code='"+billCode+"'  and status <>'3'  "
            + "group by sku,batch_code,product_date,expire_date,error_flag ";
        return jdbcTemplate.queryForList(sql);
    }
	
	/**
	 * 根据LPN号统计LPN中所有SKU的收货数量
	 * @param lpn
	 */
	public List<Map<String,Object>> getReceiptDetailSKUByLpn(String lpn,String billCode){
//		String sql = "select sku as sku,error_flag as errorFlag,sum(coalesce(un_up_num,0)) as unUpNum from tt_up_task where lpn_no='"+lpn+"'  and status = '0'  group by sku,error_flag ";
//		return jdbcTemplate.queryForList(sql);
		String sql="select b.sku as sku,a.sku_name as skuName,b.errorFlag,(b.countNum-b.upNum) as unUpNum,lpn_no as lpn "
				    +" from edi_goods_sku a, "
				    +"( "
				    +" select bill_code,customer_code,warehouse_code,sku, "
				    +" case when error_flag='noterror' then '完好'"
					+" when error_flag='error'then '破损'"
					+" END AS errorFlag,sum(count_num) as countNum,sum(up_num) as upNum,lpn_no "
					+" from tt_up_task where bill_code='"+billCode+"' and lpn_no='"+lpn+"' and status='0' "
					+" group by bill_code,bill_code,customer_code,warehouse_code,sku,lpn_no "
					+") b where a.customer_code=b.customer_code and a.sku_id=b.sku";
		return jdbcTemplate.queryForList(sql);
	}
	
}
